package project.dao;


import java.sql.PreparedStatement;
import java.sql.ResultSet;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import project.model.PrTestProject;
import models.util.MySqlDataAccessHelper;


public class TestProjectDAO {
	static DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	//	public void insertTestProject(String title, String text, String idProject, String idEmployee) throws SQLException {
	//		Connection conn = null;
	//		LogChangeDAO ldao = new LogChangeDAO();
	//        PreparedStatement pstmt = null;
	//        String id = new Date().getTime() + "";
	//        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
	//        
	//        
	//		String sql = "insert into pr_testproject values(?,?,?,?,?,?,?,?)";
	//        try {
	//            conn = getDBPoolConnection();            
	//            pstmt = conn.prepareStatement(sql);
	//            pstmt.setString(1, id);
	//            pstmt.setString(2, idProject);
	//            pstmt.setString(3, idEmployee);
	//            pstmt.setString(4, title);
	//            pstmt.setString(5, text);
	//            pstmt.setString(6, format.format(new Date()));
	//            pstmt.setInt(7, 0);
	//            pstmt.setInt(8, 0);
	//            int action = pstmt.executeUpdate();
	////            if(action > 0){
	////            	ldao.insertLogChange(idEmployee, id, title, text, 0);
	////            }
	//        } catch (Exception ex){
	//            ex.printStackTrace(System.out);
	//        }finally {
	//            helper.close();
	//        }
	//	}

	public static void insertError(String idProject,String idEmployee,String title,String text,String database,String namecolor){
		MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
		try{
			helper.open(database);
			String id = new Date().getTime() + "";
			title += "-%$#@!-" + namecolor;
			//			String sql = "Insert into pr_testproject values("+id+","+testPr.getIdProject()+","+testPr.getNameEmployee()+","+
			//				testPr.getTitle()+","+testPr.getText()+","+format.format(new Date())+",0,0)";
			String sql = "insert into pr_testproject" +
			" values(?,?,?,?,?,?,?,?)";
			PreparedStatement pstmt = helper.getConnection().prepareStatement(sql);
			pstmt.setString(1, id);
			pstmt.setString(2, idProject);
			pstmt.setString(3, idEmployee);
			pstmt.setString(4, title);
			pstmt.setString(5, text);
			pstmt.setString(6, format.format(new Date()));
			pstmt.setInt(7, 0);
			pstmt.setInt(8, 0);
			pstmt.executeUpdate();
		}catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			helper.close();
		}
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public static List loadListErrPG(int limit, int pageN,String idpro,String db,String lang){
		List map = new ArrayList();
		int count=0;
		ArrayList<PrTestProject> lst = new ArrayList<PrTestProject>();
		MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
		try{
			helper.open(db);
//			String sql="select count(pr_testproject.IDTest) as sl " +
//					"from pr_testproject " +
//					"where pr_testproject.IDProject='"+idpro+"' and pr_testproject.Delete=0";
			String sql="select count(pr_testproject.IDTest) as sl " +
			"from pr_testproject " +
			"where pr_testproject.IDProject='"+idpro+"' ";
			//System.out.println(sql);
			ResultSet rs= helper.executeQuery(sql);
			if(rs.next()){
				count=rs.getInt("sl");
			}
			rs.close();
			
//			sql="select pr_testproject.TextTest,employee.FullName," +
//					"pr_testproject.IDEmployee," +
//					"pr_testproject.IDTest," +
//					"pr_testproject.Title," +
//					"pr_testproject.Time " +
//					"from pr_testproject,employee " +
//					"where pr_testproject.IDEmployee = employee.IDEmployee " +
//					"and pr_testproject.IDProject='"+idpro+"' " +
//							"and pr_testproject.Delete=0 limit "+limit * (pageN - 1)+","+limit;
			sql="select pr_testproject.TextTest,employee.FullName," +
			"pr_testproject.IDEmployee," +
			"pr_testproject.IDTest," +
			"pr_testproject.Title," +
			"pr_testproject.Time " +
			"from pr_testproject,employee " +
			"where pr_testproject.IDEmployee = employee.IDEmployee " +
			"and pr_testproject.IDProject='"+idpro+"' " +
					"limit "+limit * (pageN - 1)+","+limit;
			rs= helper.executeQuery(sql);
			PrTestProject pt;
			while(rs.next()){
				pt=new PrTestProject();
				pt.setDb(db);
				pt.setIdlang(lang);
				pt.setIdTest(rs.getString("IDTest"));
//				pt.setIdProject(rs.getString("IDProject"));
				//pt.setIdComposition(rs.getString("IDEmployee"));
				pt.setEmployeeName(rs.getString("FullName"));
				pt.setTitle(rs.getString("Title"));
				pt.setText(rs.getString("TextTest"));
//				pt.setTime(rs.getDate("Time"));
				pt.setDisplayDate(rs.getDate("Time").toString());
//				pt.setStatus(rs.getInt("Status"));
//				pt.setDelete(rs.getInt("Delete"));
				lst.add(pt);
			}
			rs.close();
			map.add(lst);
			map.add(count);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			helper.close();
		}
		return map;
	}
	
	public ArrayList<PrTestProject> loadListErr(String idpro,String db,String lang){
		ArrayList<PrTestProject> lst = new ArrayList<PrTestProject>();
		MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
		try{
			helper.open(db);
			String sql="select pr_testproject.TextTest,pr_testproject.IDEmployee,pr_testproject.IDTest,pr_testproject.Title,pr_testproject.Time from pr_testproject " +
					"where pr_testproject.IDProject='"+idpro+"' and pr_testproject.Delete=0";
			ResultSet rs= helper.executeQuery(sql);
			PrTestProject pt;
			while(rs.next()){
				pt=new PrTestProject();
				pt.setDb(db);
				pt.setIdlang(lang);
				pt.setIdTest(rs.getString("IDTest"));
//				pt.setIdProject(rs.getString("IDProject"));
				pt.setIdComposition(rs.getString("IDEmployee"));
				pt.setTitle(rs.getString("Title"));
				pt.setText(rs.getString("TextTest"));
//				pt.setTime(rs.getDate("Time"));
				pt.setDisplayDate(rs.getDate("Time").toString());
//				pt.setStatus(rs.getInt("Status"));
//				pt.setDelete(rs.getInt("Delete"));
				lst.add(pt);
			}
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			helper.close();
		}
		return lst;
	}
	
	public static PrTestProject oneErr(String db,String idTest,String lang){
		MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
		PrTestProject pt = new PrTestProject();
		try{
			helper.open(db);
			String sql="select * from pr_testproject where pr_testproject.IDTest='"+idTest+"' and pr_testproject.Delete=0 ";
			ResultSet rs= helper.executeQuery(sql);
			if(rs.next()){
				pt=new PrTestProject();
				pt.setDb(db);
				pt.setIdlang(lang);
				pt.setIdTest(rs.getString("IDTest"));
				pt.setIdProject(rs.getString("IDProject"));
				pt.setIdComposition(rs.getString("IDEmployee"));
				pt.setTitle(rs.getString("Title"));
				pt.setText(rs.getString("TextTest"));
				pt.setTime(rs.getDate("Time"));
				pt.setDisplayDate(rs.getDate("Time").toString());
				pt.setStatus(rs.getInt("Status"));
				pt.setDelete(rs.getInt("Delete"));
				
			}
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			helper.close();
		}
		
		return pt;
	}


//	public  ArrayList<PrTestProject> getListErrorByIDProject(String databasename,String id, int status){
//
//		ArrayList<PrTestProject> arrayTestPro=new ArrayList<PrTestProject>();
//		MySqlDataAccessHelper hep = new MySqlDataAccessHelper();
//		PreparedStatement pr;
//		try{
//			hep.open(databasename);
//			String sql="";
////			if(status == 2){
////				sql = "SELECT tp.IDTest, tp.IDProject, tp.IDEmployee, tp.Title, tp.TextTest, tp.Time, tp.`Status`,e.FullName,tp.`Delete` "+
////				" FROM pr_testproject tp, employee e WHERE tp.IDProject = ? AND e.IDEmployee = tp.IDEmployee "+
////				" AND tp.`Delete` = 1 ORDER BY IDTest DESC";
////				pr=hep.getConnection().prepareStatement(sql);
////				pr.setString(1, id);
////			} else {
////				sql = "SELECT tp.IDTest, tp.IDProject, tp.IDEmployee, tp.Title, tp.TextTest, tp.Time, tp.`Status`,e.FullName,tp.`Delete` "+
////				" FROM pr_testproject tp, employee e WHERE tp.IDProject = ? AND e.IDEmployee = tp.IDEmployee "+
////				" AND tp.Status = ? AND tp.`Delete` = 0 ORDER BY IDTest DESC";
////				pr=hep.getConnection().prepareStatement(sql);
////				pr.setString(1, id);
////				pr.setInt(2, status);
////			}
////			ResultSet rs= pr.executeQuery();
//			if(status == 2){
//    			sql = "SELECT tp.IDTest, tp.IDProject, tp.IDEmployee, tp.Title, tp.TextTest, tp.Time, tp.`Status`,e.FullName,tp.`Delete` "+
//    				  " FROM pr_testproject tp, employee e WHERE tp.IDProject = "+id+" AND e.IDEmployee = tp.IDEmployee "+
//    				  " AND tp.`Delete` = 1 ORDER BY IDTest DESC";
//    		} else {
//    			sql = "SELECT tp.IDTest, tp.IDProject, tp.IDEmployee, tp.Title, tp.TextTest, tp.Time, tp.`Status`,e.FullName,tp.`Delete` "+
//    				  " FROM pr_testproject tp, employee e WHERE tp.IDProject = "+id+" AND e.IDEmployee = tp.IDEmployee "+
//    				  " AND tp.Status = "+status+" AND tp.`Delete` = 0 ORDER BY IDTest DESC";
//    		}
//        	ResultSet rs = hep.executeQuery(sql);   
//			while(rs.next()){       
//				arrayTestPro.add(new PrTestProject(rs.getString("IDTest"), rs.getString("IDProject"),
//						rs.getString("IDEmployee"), rs.getString("Title"), rs.getString("TextTest"), 
//						rs.getTimestamp("Time"), rs.getInt("Status"), rs.getInt("Delete"), rs.getString("FullName")));
//			}
//		}
//		catch (Exception e) {
//			e.printStackTrace();
//		}
//		finally{
//			hep.close();
//		}
//		return arrayTestPro;
//	}
	public static void updateTestStatus(String id,String database,String title){
        PreparedStatement pstmt = null;
        title +=  "-%$#@!-" + "#3DA850";
        MySqlDataAccessHelper help = new MySqlDataAccessHelper();
//		String sql = String.format("UPDATE pr_testproject SET Status = 1,title='"+title+"' WHERE IDTest = '%s'", id);
        String sql = "UPDATE pr_testproject SET Status = 1,title=? WHERE IDTest = ?";
        try {
        	help.open(database);
            pstmt = help.getConnection().prepareStatement(sql);
            pstmt.setString(1,title);
            pstmt.setString(2, id);
            pstmt.executeUpdate();           
        } catch (Exception ex){
            ex.printStackTrace(System.out);
        }finally {
            help.close();
        }
	}
	
	public static void updateDetailTest(String title, String newError, String idTestPro,String database){
        PreparedStatement pstmt = null;
        MySqlDataAccessHelper help = new MySqlDataAccessHelper();
		String sql = "UPDATE pr_testproject SET Title = ?, TextTest = ? WHERE IDTest = ?";
        try {
            help.open(database);
            pstmt =help.getConnection().prepareStatement(sql);
            pstmt.setString(1, title);
            pstmt.setString(2, newError);
            pstmt.setString(3, idTestPro);
            pstmt.executeUpdate();           
        } catch (Exception ex){
            ex.printStackTrace(System.out);
        }finally {
            help.close();
        }
	}
	public static void deleteTestProject(String idTest,String database){
        PreparedStatement pstmt = null;
        MySqlDataAccessHelper help = new MySqlDataAccessHelper();
		String sql = "DELETE FROM pr_testproject WHERE pr_testproject.IDTest = ?";
        try {
        	help.open(database);
            pstmt = help.getConnection().prepareStatement(sql);
            pstmt.setString(1, idTest);
            pstmt.executeUpdate();
        } catch (Exception ex){
            ex.printStackTrace(System.out);
        }finally {
            help.close();
        }
	}
	
	public static void activeTestTemp(String idTest,String database){
        PreparedStatement pstmt = null;
        MySqlDataAccessHelper help = new MySqlDataAccessHelper();
        
        String sql = "UPDATE pr_testproject SET `Delete` = 0 WHERE IDTest = ?";
        try {
        	help.open(database);
            pstmt = help.getConnection().prepareStatement(sql);
            pstmt.setString(1, idTest);
            pstmt.executeUpdate();           
        } catch (Exception ex){
            ex.printStackTrace(System.out);
        }finally {
            help.close();
        }
	}
	
//	public  ArrayList<PrTestProject> getListErrorByIDProject(String databasename,String id){
//
//		PrTestProject prtest ;
//		ArrayList<PrTestProject> arrayTestPro=new ArrayList<PrTestProject>();
//		MySqlDataAccessHelper hep = new MySqlDataAccessHelper();
//		try{
//			hep.open(databasename);
//			String sql="";
//			
//    			sql = "SELECT tp.IDTest, tp.IDProject, tp.IDEmployee, tp.Title, tp.TextTest, tp.Time, tp.`Status`,e.FullName,tp.`Delete` "+
//    				  " FROM pr_testproject tp, employee e WHERE tp.IDProject = "+id+" AND e.IDEmployee = tp.IDEmployee "+
//    				  " ORDER BY IDTest DESC";
//    		
//        	ResultSet rs = hep.executeQuery(sql);   
//        	
//			while(rs.next()){       
//				arrayTestPro.add(new PrTestProject(rs.getString("IDTest"), rs.getString("IDProject"),
//						rs.getString("IDEmployee"), rs.getString("Title"), rs.getString("TextTest"), 
//						rs.getTimestamp("Time"), rs.getInt("Status"), rs.getInt("Delete"), rs.getString("FullName")));
//			}
//		}
//		catch (Exception e) {
//			e.printStackTrace();
//		}
//		finally{
//			hep.close();
//		}
//		return arrayTestPro;
//	}
	
	public  ArrayList<PrTestProject> getListErrorByIDProject(String databasename,String id){

		PrTestProject prtest ;
		ArrayList<PrTestProject> arrayTestPro=new ArrayList<PrTestProject>();
		MySqlDataAccessHelper hep = new MySqlDataAccessHelper();
		try{
			hep.open(databasename);
			String sql="";
			
    			sql = "SELECT tp.IDTest, tp.IDProject, tp.IDEmployee, tp.Title, tp.TextTest, tp.Time, tp.`Status`,e.FullName,tp.`Delete` "+
    				  " FROM pr_testproject tp, employee e WHERE tp.IDProject = "+id+" AND e.IDEmployee = tp.IDEmployee "+
    				  " ORDER BY IDTest DESC";
    		
        	ResultSet rs = hep.executeQuery(sql);   
        	
			while(rs.next()){       
//				arrayTestPro.add(new PrTestProject(rs.getString("IDTest"), rs.getString("IDProject"),
//						rs.getString("IDEmployee"), rs.getString("Title"), rs.getString("TextTest"), 
//						rs.getTimestamp("Time"), rs.getInt("Status"), rs.getInt("Delete"), rs.getString("FullName")));
				
				prtest = new PrTestProject();
//				prtest.setDb(databasename);
//				prtest.setIdlang(lang);
				prtest.setIdTest(rs.getString("IDTest"));
				prtest.setIdProject(rs.getString("IDProject"));
				prtest.setIdComposition(rs.getString("IDEmployee"));
				prtest.setTitle(rs.getString("Title"));
				prtest.setText(rs.getString("TextTest"));
				prtest.setTime(rs.getDate("Time"));
				prtest.setDisplayDate(rs.getDate("Time").toString());
				prtest.setStatus(rs.getInt("Status"));
				prtest.setDelete(rs.getInt("Delete"));
				prtest.setEmployeeName(rs.getString("FullName"));
				arrayTestPro.add(prtest);
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			hep.close();
		}
		return arrayTestPro;
	}
}
